/**
 * 
 */
package org.chungyan5.mnt.lib.dbImpl.mysql;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import org.chungyan5.mnt.lib.Item;
import org.chungyan5.mnt.lib.MNTTag;
import org.chungyan5.mnt.lib.Db;
import org.chungyan5.mnt.lib.MNTTagComparator;

/**
 * @author yan, chungyan5@gmail.com
 *
 */
public class DbImpl implements Db, Serializable {

	private static final long serialVersionUID = 1470173280066637825L;
	
	private MNTTagComparator mntTagComparator = new MNTTagComparator();
	
	/**
	 * 
	 */
	public static final String TABLE_TAG = ShareData.TABLE_TAG;
	public static final String TABLE_TAG_COL_TAG_PTR = ShareData.TABLE_TAG_COL_TAG_PTR;
	public static final String TABLE_TAG_COL_TAG_NAME = ShareData.TABLE_TAG_COL_TAG_NAME;

	public static final String TABLE_ITEM = ShareData.TABLE_ITEM;
	public static final String TABLE_ITEM_COL_ITEM_PTR = ShareData.TABLE_ITEM_COL_ITEM_PTR;
	public static final String TABLE_ITEM_COL_ITEM_NAME = ShareData.TABLE_ITEM_COL_ITEM_NAME;
	public static final String TABLE_ITEM_COL_ITEM_DESCRIPTION = ShareData.TABLE_ITEM_COL_ITEM_DESCRIPTION;
	public static final String TABLE_ITEM_COL_ITEM_EMPTY = ShareData.TABLE_ITEM_COL_ITEM_EMPTY;

	public static final String TABLE_TAGS_ITEMS = ShareData.TABLE_TAGS_ITEMS;
	public static final String TABLE_TAGS_ITEMS_COL_PTR = ShareData.TABLE_TAGS_ITEMS_COL_PTR;
	public static final String TABLE_TAGS_ITEMS_COL_ITEM_PTRS = ShareData.TABLE_TAGS_ITEMS_COL_ITEM_PTRS;
	public static final String TABLE_TAGS_ITEMS_COL_TAG_PTRS = ShareData.TABLE_TAGS_ITEMS_COL_TAG_PTRS;
	
	private Connection connect = null; 	// Database objects 
	private Statement statement = null; 	// 執行,傳入之sql為完整字串
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	private String loginName = "";
	private String pw = "";
	
	public DbImpl(String loginName, String pw) {
		this.loginName = loginName;
		this.pw = pw;
	}

	@Override
	public boolean open() {

		try {
			
			// connect JDBC Mysql driver ===================================================================
			Class.forName("com.mysql.jdbc.Driver");				// This will load the MySQL driver, each DB has its own driver
		
			connect = DriverManager.getConnection(					// Setup the connection with the DB, 註冊driver 
				"jdbc:mysql://localhost/mnt_java_lib?",			// localhost是主機名, mnt_java_lib是database名 
				this.loginName, this.pw); 
	
			// chk DB, if !existing, create the whole structure, otherwise skip to create new one ===========
			statement = connect.createStatement();					// Statements allow to issue SQL queries to the database
//			statement.executeUpdate("CREATE DATABASE IF NOT EXISTS " + "mnt_java_lib" + " ;");
			statement.executeUpdate("create table IF NOT EXISTS " + TABLE_TAG + "(" + TABLE_TAG_COL_TAG_PTR 
					+ " INTEGER AUTO_INCREMENT PRIMARY KEY, " + TABLE_TAG_COL_TAG_NAME + " TEXT);");
			statement.executeUpdate("create table IF NOT EXISTS " + TABLE_ITEM + "(" 
					+ TABLE_ITEM_COL_ITEM_PTR + " INTEGER AUTO_INCREMENT PRIMARY KEY, "
					+ TABLE_ITEM_COL_ITEM_NAME + " TEXT, " 
					+ TABLE_ITEM_COL_ITEM_DESCRIPTION + " TEXT, " 
					+ TABLE_ITEM_COL_ITEM_EMPTY + " INTEGER " 
					+ ");");
			statement.executeUpdate("create table IF NOT EXISTS " + TABLE_TAGS_ITEMS + "(" 
					+ TABLE_TAGS_ITEMS_COL_PTR + " INTEGER AUTO_INCREMENT PRIMARY KEY, "
					+ TABLE_TAGS_ITEMS_COL_ITEM_PTRS + " INTEGER, " 
					+ TABLE_TAGS_ITEMS_COL_TAG_PTRS + " INTEGER);");
			
			return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			close();
			return false;
		} catch (SQLException e) {				
			e.printStackTrace();
			close();
			return false;
		}
		
	}

	@Override
	public void close() {
		
		try {
			if (resultSet!=null)
				resultSet.close();
			if (statement!=null)
				statement.close();
			if (connect!=null)
				connect.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/*	public class DbReturnDataImp {
		
		private ArrayList<ArrayList<Object>> arrayListRows = new ArrayList<ArrayList<Object>>();	// 2-dimensions Objects
		
		private int pRow; 
//		private int topRow = -1;
		
		private SQLiteStatement st;

		public DbReturnDataImp(SQLiteStatement st) {
			pRow = -1;
//			topRow = -1;
			this.st = st;
		}

		public void close() {
			st.dispose();
		}

		public int getInt(int columnIndex) {
			return (Integer) arrayListRows.get(pRow).get(columnIndex);
		}

		public long getLong(int columnIndex) {
			// fr. Desktop sqlite4java, it returns a integer type
			return ((Integer) arrayListRows.get(pRow).get(columnIndex)).longValue();
		}

		public String getString(int columnIndex) {
			return String.valueOf(arrayListRows.get(pRow).get(columnIndex));
		}

		public boolean moveToNext() {
			try {
				if (!st.step()) {
					st.dispose();
				} else {
					if (st.hasRow()) {
//						topRow++;
						pRow++;
						int columns = st.columnCount();
						ArrayList<Object> arrayListColumn = new ArrayList<Object>();
						for(int column = 0 ; column < columns ; column++)
							arrayListColumn.add(st.columnValue(column));
						arrayListRows.add(arrayListColumn);
						return true;
					} else {
						st.dispose();
					}
				}
			} catch (SQLiteException e) {
				e.printStackTrace();
			}
			return false;
		}

		public int getCount() {
			// using SELECT COUNT(*) FROM " + tableName
			//prepareQuery("SELECT last_insert_rowid();");
			//DbReturnDataImp dbReturnDataImp = new DbReturnDataImp();
			//if (dbReturnDataImp.moveToNext()) retRowId = dbReturnDataImp.getLong(0);
//			else retRowId = -1;
//			dbReturnDataImp.close();
			return arrayListRows.size();
		}

		public int getColumnIndex(String columnName) {
			// TODO Auto-generated method stub
			return 0;
		}
		
		/**
		 * auto. loading all data fr. database and save into Array, 
		 * let user to access data for Array only; user does not handle any 
		 * database operation
		 * @return ArrayList
		 */
/*		public ArrayList<ArrayList<Object>> toArray() {
			
			// load all data
			while (moveToNext());
			return arrayListRows;
		}

	}*/

	@Override
	public MNTTag newTag(MNTTag tag) {
		
		long retRowId = -1;
		
		// PreparedStatements can use variables and are more efficient
		try {
			preparedStatement = connect
			      .prepareStatement("INSERT INTO " 
					  	+ TABLE_TAG + " (" 
				  		+ TABLE_TAG_COL_TAG_NAME 
				  		+ ") VALUES(" 
				  		+ " ? " 
				  		+ ");");
			preparedStatement.setString(1, tag.getName());
			retRowId = preparedStatement.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    
		// 0=< means cannot insert; >0 means successful and it is the new created record id
		if (retRowId> 0) {
			tag.setId(retRowId);
			return tag;
		} else 
			return null;
		
	}

	@Override
	public List<MNTTag> queryTags() {
		
		final String sql = "SELECT " 
				+ TABLE_TAG_COL_TAG_NAME + "," 
				+ TABLE_TAG_COL_TAG_PTR 
				+ " FROM " + TABLE_TAG 
				+ " ORDER BY " + TABLE_TAG_COL_TAG_NAME + " ASC"
				+ ";";
		
		// ArrayList object for original DB order, do not need to comparison of Tag ID
		ArrayList<MNTTag> hs = new ArrayList<MNTTag>();
		
		try {
			resultSet = statement.executeQuery(sql);
			
			while (resultSet.next()) {
				MNTTag tag = new MNTTag(resultSet.getString(1));	// get tag name
				tag.setId(resultSet.getLong(2));					// get tag id
				hs.add(tag);
			}
			resultSet.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return hs;
	}

	@Override
	public MNTTag queryTag(long id) {
		final String sql = "SELECT " 
				+ TABLE_TAG_COL_TAG_NAME 
				+ " FROM " + TABLE_TAG 
				+ " WHERE " + TABLE_TAG_COL_TAG_PTR + "=" + id
				+ ";";
		
		MNTTag tag = null;
		
		try {
			resultSet = statement.executeQuery(sql);
			
			while (resultSet.next()) {
				tag = new MNTTag(resultSet.getString(1));	// get tag name
				tag.setId(id);								// get tag id
			}
			resultSet.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return tag;
	}

	@Override
	public MNTTag updateTag(MNTTag tag) {
		
		if (update(TABLE_TAG, TABLE_TAG_COL_TAG_NAME + "='" + tag.getName() + "'",
				TABLE_TAG_COL_TAG_PTR + "=" + tag.getId()
				) ) 
			return tag;
		else return null;
	}

	@Override
	public boolean removeTag(MNTTag tag) {
		if (remove(TABLE_TAG, TABLE_TAG_COL_TAG_PTR + "=" + tag.getId())>=0)
			return true;			// record(s) deleted, so del. successful
		else return false;
	}

	@Override
	public boolean removeTagFrItemTagRel(MNTTag tag) {
		if (remove(TABLE_TAGS_ITEMS, TABLE_TAG_COL_TAG_PTR + "=" + tag.getId())>=0)
			return true;			// record(s) deleted, so del. successful
		else return false;
	}

	/**
	 * @param table remove record(s) from this table
	 * @param whereClause sql where cause " WHERE " + XXXXXXX + ";"
	 * @return no. of record removed or nothing to remove as -1
	 */
	private int remove(String table, String whereClause) {
		
		try {
			
		// rd back how many row(s) to be deleted =================================
			return statement.executeUpdate("DELETE FROM " + table		// deleted record, no exception, so updated successfully 
					+ " WHERE " + whereClause + ";");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
	}
	
	@Override
	public Item newItem(Item item) {
		
		int emptyFlag;
		if (item.isEmptyTag()) emptyFlag = 1;
		else emptyFlag = 0;
		
		long retRowId = -1;
		try {
			
		// insert a new item ======================================================
			preparedStatement = connect
			      .prepareStatement("INSERT INTO " + TABLE_ITEM 
							+ " (" + TABLE_ITEM_COL_ITEM_NAME + ", " + TABLE_ITEM_COL_ITEM_DESCRIPTION + ", " + TABLE_ITEM_COL_ITEM_EMPTY 
							+ ") VALUES(" 
							+ " ? " + ", " + " ? " + ", " + " ? "
							+ ");");
			    		  
			preparedStatement.setString(1, item.getName());
			preparedStatement.setString(2, item.getDescription());
			preparedStatement.setInt(3, emptyFlag);
			retRowId = preparedStatement.executeUpdate();

			// get back a new item auto. generated ID ============================
			if (retRowId==1) {
				final String sql = "SELECT LAST_INSERT_ID();";
				resultSet = statement.executeQuery(sql);
					
				while (resultSet.next()) {
					retRowId = resultSet.getLong(1);
				}
				resultSet.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 0=< means cannot insert; >0 means successful and it is the new created record id
		if (retRowId> 0) {
				item.setId(retRowId);
				return item;
		} else 
			return null;
		
	}

	@Override
	public Item queryItem(long id) {
		final String sql = "SELECT " 
				+ TABLE_ITEM_COL_ITEM_NAME + "," 
				+ TABLE_ITEM_COL_ITEM_DESCRIPTION + "," 
				+ TABLE_ITEM_COL_ITEM_EMPTY
				+ " FROM " + TABLE_ITEM 
				+ " WHERE " + TABLE_ITEM_COL_ITEM_PTR + "=" + id
				+ ";";
		
		Item item = null;
		
		try {
			resultSet = statement.executeQuery(sql);
			
			while (resultSet.next()) {
				item = new Item(resultSet.getString(1));				// get item name
				item.setDescription(resultSet.getString(2));			// get item description
				if (resultSet.getInt(3)==1) item.setEmptyTag(true);		// get item empty flag
				else item.setEmptyTag(false);
				item.setId(id);											// get item id
			}
			resultSet.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return item;
	}

	@Override
	public List<Item> queryItemsWithEmptyTag() {
		
		final String sql = "SELECT " 
				+ TABLE_ITEM_COL_ITEM_NAME + "," 
				+ TABLE_ITEM_COL_ITEM_DESCRIPTION + "," 
				+ TABLE_ITEM_COL_ITEM_EMPTY + "," 
				+ TABLE_ITEM_COL_ITEM_PTR 
				+ " FROM " + TABLE_ITEM
				+ " WHERE " + TABLE_ITEM_COL_ITEM_EMPTY + "='1'" 
				+ " ORDER BY " + TABLE_ITEM_COL_ITEM_NAME + " ASC"
				+ ";";

		ArrayList<Item> hs = new ArrayList<Item>();
		
		try {
			resultSet = statement.executeQuery(sql);
			
			while (resultSet.next()) {
				Item item = new Item(resultSet.getString(1));			// get item name
				item.setDescription(resultSet.getString(2));			// get item description
				if (resultSet.getInt(3)==1) item.setEmptyTag(true);		// get item empty flag
				else item.setEmptyTag(false);
				item.setId(resultSet.getLong(4));						// get item id
				hs.add(item);
			}
			resultSet.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return hs;
	}

	@Override
	public List<Item> queryItems(Set<MNTTag> selectedPlusTags, Set<MNTTag> selectedNegTags) {

		// create SQL for selecting item(s) info. ==========================
			/* 
			 * SELECT Items.ItemName, Items.ItemDescription, Items.ItemEmpty, Items._id FROM Items  
			 *	-- select item info.
			 *
			 * INNER JOIN 
			 * (Select PlusTable.ItemPointers from 
			 * 	(
			 *	-- connect this item info. to the following selected item pointer(s)
			 *
			 */
		String sql = "SELECT " 
				+ TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_NAME + ", " 
				+ TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_DESCRIPTION + ", " 
				+ TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_EMPTY + ", " 
				+ TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_PTR + " FROM " 
				+ TABLE_ITEM 
				+ " INNER JOIN "
				+ "(SELECT " + "PlusTable" + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS + " FROM " 
				+ "( ";
		
		// create SQL for filtering selected tag(s) ==========================
		if (selectedPlusTags.size()==0) {
			/* 
			 * SELECT TagsItems.ItemPointers FROM TagsItems 
			 * 		UNION 
			 * 		SELECT Items._id FROM Items WHERE Items.ItemEmpty=1  
			 * -- select item(s) at tag and item relation and Item with empty relation; Originally it can be replaced by "SELECT _id FROM Items", 
			 * 		but the o/p as Items._id, we need TagsItems.ItemPointers in the following EXCEPT ... 
			 * 
			SELECT Items.ItemName,Items.ItemDescription,Items.ItemEmpty,Items._id FROM Items
			INNER JOIN 
			(
			Select PlusTable.ItemPointers from 
				(
				SELECT TagsItems.ItemPointers FROM TagsItems
				UNION
				SELECT Items._id FROM Items WHERE Items.ItemEmpty=1
				) As PlusTable
			WHERE PlusTable.ItemPointers NOT IN
				(
				SELECT MinusTable.ItemPointers FROM TagsItems MinusTable WHERE 
				MinusTable.TagPointers=4
				OR 
				MinusTable.TagPointers=3 
				)
			) as PlusNegTable
			ON ItemPointers=Items._id ORDER BY Items.ItemName ASC;
			*/
			sql += "SELECT " + TABLE_TAGS_ITEMS + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS 
					+ " FROM " + TABLE_TAGS_ITEMS
					+ " UNION "
					+ "SELECT " + TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_PTR 
					+ " FROM " + TABLE_ITEM
					+ " WHERE " + TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_EMPTY + "=" + 1;
			
		} else {
			/*
			 * SELECT InterSectTags0.ItemPointers FROM TagsItems InterSectTags0
			 * 		INNER JOIN 
			 * 		TagsItems InterSectTags1 ON InterSectTags0.ItemPointers = InterSectTags1.ItemPointers
			 * 		INNER JOIN 
			 * 		TagsItems InterSectTags2 ON InterSectTags0.ItemPointers = InterSectTags2.ItemPointers
			 * 		WHERE 
			 * 		InterSectTags0.TagPointers=7
			 * 		AND
			 * 		InterSectTags1.TagPointers=12
			 * 		AND
			 * 		InterSectTags2.TagPointers=15
			 * -- select item(s) at tag(s) by taking their INTERSECT values(s)
			 * 
			SELECT Items.ItemName, Items.ItemDescription, Items.ItemEmpty, Items._id FROM Items  
			INNER JOIN 
			(
			Select PlusTable.ItemPointers from 
				(
				SELECT InterSectTags0.ItemPointers FROM TagsItems InterSectTags0
					INNER JOIN 
					TagsItems InterSectTags1 ON InterSectTags0.ItemPointers = InterSectTags1.ItemPointers
					INNER JOIN 
					TagsItems InterSectTags2 ON InterSectTags0.ItemPointers = InterSectTags2.ItemPointers
					WHERE 
					InterSectTags0.TagPointers=7
					AND
					InterSectTags1.TagPointers=12
					AND
					InterSectTags2.TagPointers=15
				) As PlusTable
			WHERE PlusTable.ItemPointers NOT IN
				(
				SELECT MinusTable.ItemPointers FROM TagsItems MinusTable WHERE 
				MinusTable.TagPointers=3 
				OR 
				MinusTable.TagPointers=4
				)
			) as PlusNegTable
			ON ItemPointers=Items._id ORDER BY Items.ItemName ASC;
			*/
			
			String postSql = "";
			int i = 0;
			Iterator<MNTTag> itr = selectedPlusTags.iterator();
			while (itr.hasNext()) {
				if (i==0) {
					sql += "SELECT " + "InterSectTags" + i + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS 
							+ " FROM " + TABLE_TAGS_ITEMS + " InterSectTags" + i;
					postSql += " WHERE " + "InterSectTags" + i + "." + TABLE_TAGS_ITEMS_COL_TAG_PTRS + "=" + itr.next().getId();  
				} else {
					sql += " INNER JOIN "
							+ TABLE_TAGS_ITEMS + " InterSectTags" + i + " ON " + "InterSectTags0" + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS 
							+ "=" + "InterSectTags" + i + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS;   
					postSql += " AND " + "InterSectTags" + i + "." + TABLE_TAGS_ITEMS_COL_TAG_PTRS + "=" + itr.next().getId();  
				}
				i++;
			}
			sql += postSql;
		}

		sql += ") As PlusTable";
		
		// create SQL for filtering non-selected tag(s) ==========================
		if (selectedNegTags.size()==0) {
			// do nothing as no any non-selected flag
		} else {
			/*
			 * WHERE PlusTable.ItemPointers NOT IN
			 * 	(
			 * 	SELECT MinusTable.ItemPointers FROM TagsItems MinusTable WHERE 
			 * 	MinusTable.TagPointers=3 
			 * 	OR 
			 * 	MinusTable.TagPointers=4
			 * 	)
			 * -- remove the item(s) from non-selected tag  
			 */ 
			sql += " WHERE " + "PlusTable" + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS + " NOT IN "
					+ "(" + " SELECT " + "MinusTable" + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS 
					+ " FROM " + TABLE_TAGS_ITEMS + " MinusTable" + " WHERE"; 
			int i = 0;
			Iterator<MNTTag> itr = selectedNegTags.iterator();
			while (itr.hasNext()) {
				if (i>0) {
					sql += " OR";
				}
				sql += " MinusTable " + "." + TABLE_TAGS_ITEMS_COL_TAG_PTRS + "=" + itr.next().getId();
				i++;
			}
			sql += ") ";
		}

		// create SQL for alphabetic order in Item Name in the result ==========================
			/*
			 * ) as PlusNegTable
			 * ON ItemPointers=Items._id 
			 * -- name as Plus and Neg Mix table and matching this Item ptr ID(s) result to the Item ID(s) for list the content of this Item    
			 * ORDER BY Items.ItemName ASC;
			 * -- in alphabetic order with Capital letters first, then small letters   
			 */ 
		sql += ") as Plus_Neg_Table ON " 
				+ TABLE_TAGS_ITEMS_COL_ITEM_PTRS + "=" + TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_PTR
				+ " ORDER BY " + TABLE_ITEM + "." + TABLE_ITEM_COL_ITEM_NAME + " ASC"
				+ ";";
		
		// execute SQL query =================================================================
		final String sqlFixed = sql;
		
		ArrayList<Item> hs = new ArrayList<Item>();
		try {
			resultSet = statement.executeQuery(sqlFixed);
			
		// convert Items object fr sql =============================================
			while (resultSet.next()) {
				Item item = new Item(resultSet.getString(1));			// get item name
				item.setDescription(resultSet.getString(2));			// get item description
				if (resultSet.getInt(3)==1) item.setEmptyTag(true);		// get item empty flag
				else item.setEmptyTag(false);
				item.setId(resultSet.getLong(4));						// get item id
				hs.add(item);
			}
			resultSet.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return hs;
	}

	@Override
	public Item updateItem(Item item) {
		int sqlEmptyFlagStr;
		if (item.isEmptyTag()) sqlEmptyFlagStr = 1;
		else sqlEmptyFlagStr = 0;
			
		if (update(TABLE_ITEM, TABLE_ITEM_COL_ITEM_NAME + "='" + item.getName() + "', " 
									+ TABLE_ITEM_COL_ITEM_DESCRIPTION + "='" + item.getDescription() + "', " 
									+ TABLE_ITEM_COL_ITEM_EMPTY + "=" + sqlEmptyFlagStr,
									TABLE_TAG_COL_TAG_PTR + "=" + item.getId()
									) ) 
			return item;
		else return null;
	}

	/**
	 * @param table update record from this table
	 * @param updateString
	 * @param whereClause sql where cause " WHERE " + XXXXXXX + ";"
	 * @return true as updated successfully, otherwise false
	 */
	public boolean update(String table, String updateString, String whereClause) {
			final String sql = "UPDATE " 
				+ table
				+ " SET "
				+ updateString 
				+ " WHERE " 
				+ whereClause
				+ ";";
			
			try {
				if (statement.executeUpdate(sql) == 1) return true;			// deleted ONE record, so updated. successful
				else return false;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
	}
	
	@Override
	public boolean removeItem(Item item) {
		if (remove(TABLE_ITEM, TABLE_ITEM_COL_ITEM_PTR + "=" + item.getId())>=0)
			return true;			// record(s) deleted, so del. successful
		else return false;
	}

	@Override
	public boolean removeFrItemTagRel(Item item) {
		if (remove(TABLE_TAGS_ITEMS, TABLE_TAGS_ITEMS_COL_ITEM_PTRS + "=" + item.getId())>=0)
			return true;			// record(s) deleted, so del. successful
		else return false;
	}

	@Override
	public boolean connectTagItem(Item item, MNTTag tag) {

		try {
			preparedStatement = connect
			      .prepareStatement("INSERT INTO " + TABLE_TAGS_ITEMS 
							+ " (" + TABLE_TAGS_ITEMS_COL_ITEM_PTRS + ", " + TABLE_TAGS_ITEMS_COL_TAG_PTRS  
							+ ") VALUES(" 
							+ " ? " + ", " + " ? "
							+ ");");
			    		  
			preparedStatement.setLong(1, item.getId());
			preparedStatement.setLong(2, tag.getId());
			if (preparedStatement.executeUpdate()>=0) return true; 
			else return false;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
				
	}

	@Override
	public boolean disConnectTagItem(Item item, MNTTag tag) {
		
		try {
			
		// rd back how many row(s) to be deleted =================================
			if (statement.executeUpdate("DELETE FROM " + TABLE_TAGS_ITEMS 
					+ " WHERE " + TABLE_TAGS_ITEMS_COL_ITEM_PTRS + "=" + item.getId()
					+ " AND " + TABLE_TAGS_ITEMS_COL_TAG_PTRS + "=" + tag.getId()
					+ ";")>=0) return true;
			else return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public Set<MNTTag> queryRelTags(Item item) {
		
		// prepare the SQL for select db =============================================
		final String sql = "SELECT " 
				+ TABLE_TAG + "." + TABLE_TAG_COL_TAG_NAME + "," 
				+ TABLE_TAG + "." + TABLE_TAG_COL_TAG_PTR
				+ " FROM " + TABLE_TAGS_ITEMS
				+ " INNER JOIN " + TABLE_TAG
				+ " ON " + TABLE_TAGS_ITEMS + "." + TABLE_TAGS_ITEMS_COL_TAG_PTRS + "=" + TABLE_TAG + "." + TABLE_TAG_COL_TAG_PTR
				+ " WHERE " + TABLE_TAGS_ITEMS + "." + TABLE_TAGS_ITEMS_COL_ITEM_PTRS + "=" + item.getId()
				+ " ORDER BY " + TABLE_TAG + "." + TABLE_TAG_COL_TAG_NAME + " ASC"
				+ ";";
		
		TreeSet<MNTTag> hs = new TreeSet<MNTTag>(mntTagComparator);
		try {
			resultSet = statement.executeQuery(sql);
			
		// convert Items object fr sql =============================================
			while (resultSet.next()) {
				MNTTag tag = new MNTTag(resultSet.getString(1));	// get tag name
				tag.setId(resultSet.getLong(2));					// get tag id
				hs.add(tag);
			}
			resultSet.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return hs;
	}

}
